﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
using System.Data.SqlClient;
using System.Net.Mail;
using System.Configuration;
using System.Collections;
using System.Web.Script.Serialization;
using System.Text;
using System.Data;

/// <summary>
/// Summary description for Application
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class Application : System.Web.Services.WebService
{

    public Application()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string SaveApplication(object application, object loading, object unloading, object step2, object step3)
    {
        object[] loading_data = loading as object[];
        object[] unloading_data = unloading as object[];
        Dictionary<string, object> app_data = application as Dictionary<string, object>;
        Dictionary<string,string> msg = new Dictionary<string,string>();

        SqlParameter[] pars = new SqlParameter[5];
        pars[0] = new SqlParameter("type_offer_id", app_data["type_offer_id"]);
        pars[0].DbType = System.Data.DbType.Int64;
        pars[1] = new SqlParameter("type_transport_id", app_data["type_transport_id"]);
        pars[1].DbType = System.Data.DbType.Int64;
        pars[2] = new SqlParameter("isActive", true);
        pars[2].DbType = System.Data.DbType.Boolean;
        pars[3] = new SqlParameter("created", DateTime.Now);
        pars[3].DbType = System.Data.DbType.DateTime;
        pars[4] = new SqlParameter("company_id", app_data["company_id"]);
        pars[4].DbType = System.Data.DbType.Int64;

        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_InsApplication", conn))
            {
                //SqlCommand cmd = new SqlCommand("SP_InsApplication", ConnectionHttpModule.ConnectionManager.CurrentConnection);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter p in pars)
                {
                    cmd.Parameters.Add(p);
                }

                int langid = DAO.Connection.DEFAULT_LANGUAGE;
                long app_id = 0;
                try
                {
                    long.TryParse(cmd.ExecuteScalar().ToString(), out app_id);

                    int max = loading_data.Length;
                    if(unloading_data.Length>max)
                    {
                        max = unloading_data.Length;
                    }
                    for (int i = 0; i < max; i++)
                    {
                        long loading_id = 0;
                        long unloading_id = 0;
                        long step2_id = 0;
                        
                        if(i<loading_data.Length)
                        {
                            InsertLoading(loading_data[i], ref loading_id);
                        }
                        if (i < unloading_data.Length)
                        {
                            InsertUnloading(unloading_data[i], ref unloading_id);
                        }

                        InsertStep2CargoTransport(step2, app_id, loading_id, unloading_id, ref step2_id);//Insert Step2 Cargo Transport
                    }

                    if (app_data["type_offer_id"].ToString() == "71")
                    {
                        InsertStep3Tender(step3, app_id);
                    }
                    else
                    {
                        switch (app_data["type_transport_id"].ToString())
                        {
                            case "166": //Rail
                            case "164": //Road
                                InsertStep3Transport(step3, app_id);
                                break;
                            case "165":
                                if (app_data["type_offer_id"].ToString() == "69")
                                {
                                    InsStep3SeaCargo(step3, app_id);
                                }
                                else
                                {
                                    InsStep3SeaTransport(step3, app_id);
                                }
                                break;
                            case "167"://Air
                                InsertStep3Air(step3, app_id);
                                break;
                        }
                    }


                    
                    
                    long type_transport_id = 0;
                    Int64.TryParse(app_data["type_transport_id"].ToString(), out type_transport_id);
                    long type_offer_id = 0;
                    Int64.TryParse(app_data["type_offer_id"].ToString(), out type_offer_id);
                    SendEmail(app_id,type_transport_id,type_offer_id);

                    msg.Add("status", "1");
                    msg.Add("message", DAO.TextBaseFactory.loadText("success_Create_Application", langid));
                }
                catch (Exception ex)
                {
                    msg.Add("status", "0");
                    msg.Add("message", ex.ToString());
                }
            }
        }

        JavaScriptSerializer js = new JavaScriptSerializer();
        string strJSON = js.Serialize(msg);
        return strJSON;
    }

    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string AddAditionalLoading(object step2, object loading, object unloading)
    {
        Dictionary<string, string> msg = new Dictionary<string, string>();
        Dictionary<string, object> step2_data = step2 as Dictionary<string, object>;
        try
        {
            long loading_id = 0;
            long unloading_id = 0;
            long step2_id = 0;
            long app_id = 0;
            long.TryParse(step2_data["app_id"].ToString(), out app_id);
            InsertLoading(loading, ref loading_id);//Insert Loading or Unloading
            InsertUnloading(unloading, ref unloading_id);
            InsertStep2CargoTransport(step2, app_id, loading_id, unloading_id, ref step2_id);//Insert Step2 Cargo Transport
            msg.Add("status", "1");
            msg.Add("message","successs");
        }
        catch (Exception ex)
        {
            msg.Add("status", "0");
            msg.Add("message", ex.ToString());
        }
        
        JavaScriptSerializer js = new JavaScriptSerializer();
        string strJSON = js.Serialize(msg);
        return strJSON;
    }

    public void InsertLoading(object loading, ref long loading_id)
    {
        Dictionary<string, object> loading_data = loading as Dictionary<string, object>;
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_InsLoadingUnloading", conn))
            {

                SqlParameter[] pars = new SqlParameter[9];
                pars[0] = new SqlParameter("country_id", loading_data["country_id"]);
                pars[0].DbType = System.Data.DbType.Int64;
                pars[1] = new SqlParameter("city", loading_data["city"]);
                pars[1].DbType = System.Data.DbType.String;
                pars[2] = new SqlParameter("postcode", loading_data["postcode"]);
                pars[2].DbType = System.Data.DbType.String;
                string loading_ampm = "AM";
                string loading_time = "0:00";
                if (loading_data["time"] != null && loading_data["ampm"] != null)
                {
                    loading_time = loading_data["time"].ToString();
                    loading_ampm = loading_data["ampm"].ToString();
                }
                pars[3] = new SqlParameter("time", loading_time + " " + loading_ampm);
                pars[3].DbType = System.Data.DbType.String;
                DateTime loading_date;
                if (DateTime.TryParse(loading_data["date"].ToString(), out loading_date))
                {
                    pars[4] = new SqlParameter("date", loading_date);
                    pars[4].DbType = System.Data.DbType.DateTime;
                }

                if (loading_data.ContainsKey("airport_id"))
                {
                    pars[5] = new SqlParameter("airport_id", loading_data["airport_id"]);
                    pars[5].DbType = System.Data.DbType.Int64;
                }
                else
                {
                    pars[5] = new SqlParameter("airport_id", 0);
                    pars[5].DbType = System.Data.DbType.Int64;
                }

                if (loading_data.ContainsKey("seaport_id"))
                {
                    pars[6] = new SqlParameter("seaport_id", loading_data["seaport_id"]);
                    pars[6].DbType = System.Data.DbType.Int64;
                }
                else
                {
                    pars[6] = new SqlParameter("seaport_id", 0);
                    pars[6].DbType = System.Data.DbType.Int64;
                }

                pars[7] = new SqlParameter("other_airport", loading_data["other_airport"]);
                pars[7].DbType = System.Data.DbType.String;
                pars[8] = new SqlParameter("other_seaport", loading_data["other_seaport"]);
                pars[8].DbType = System.Data.DbType.String;
                
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter p in pars)
                {
                    if (p != null)
                    {
                        if (p.DbType == System.Data.DbType.Int64)
                        {
                            if (p != null && p.Value != null)
                            {
                                if (p.Value.ToString() != "0" && p.Value.ToString() != "")
                                    cmd.Parameters.Add(p);
                            }
                        }
                        else
                        {
                            if (p != null && p.Value != null)
                            {
                                if (p.Value.ToString() != "0")
                                    cmd.Parameters.Add(p);
                            }
                        }
                    }
                }
                long.TryParse(cmd.ExecuteScalar().ToString(), out loading_id);
            }
        }
    }

    public void InsertUnloading(object unloading, ref long unloading_id)
    {
        Dictionary<string, object> unloading_data = unloading as Dictionary<string, object>;
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_InsLoadingUnloading", conn))
            {
                //Unloading
                SqlParameter[] pars = new SqlParameter[9];
                pars[0] = new SqlParameter("country_id", unloading_data["country_id"]);
                pars[0].DbType = System.Data.DbType.Int64;
                pars[1] = new SqlParameter("city", unloading_data["city"]);
                pars[1].DbType = System.Data.DbType.String;
                pars[2] = new SqlParameter("postcode", unloading_data["postcode"]);
                pars[2].DbType = System.Data.DbType.String;
                string unloading_ampm = "AM";
                string unloading_time = "0:00";
                if (unloading_data["time"] != null && unloading_data["ampm"] != null)
                {
                    unloading_time = unloading_data["time"].ToString();
                    unloading_ampm = unloading_data["ampm"].ToString();
                }
                pars[3] = new SqlParameter("time", unloading_time + " " + unloading_ampm);

                pars[3].DbType = System.Data.DbType.String;
                DateTime unloading_date;
                if (DateTime.TryParse(unloading_data["date"].ToString(), out unloading_date))
                {
                    pars[4] = new SqlParameter("date", unloading_date);
                    pars[4].DbType = System.Data.DbType.DateTime;
                }
                if (unloading_data.ContainsKey("airport_id"))
                {
                    pars[5] = new SqlParameter("airport_id", unloading_data["airport_id"]);
                    pars[5].DbType = System.Data.DbType.Int64;
                }
                else
                {
                    pars[5] = new SqlParameter("airport_id", 0);
                    pars[5].DbType = System.Data.DbType.Int64;
                }

                if (unloading_data.ContainsKey("seaport_id"))
                {
                    pars[6] = new SqlParameter("seaport_id", unloading_data["seaport_id"]);
                    pars[6].DbType = System.Data.DbType.Int64;
                }
                else
                {
                    pars[6] = new SqlParameter("seaport_id", 0);
                    pars[6].DbType = System.Data.DbType.Int64;
                }
                pars[7] = new SqlParameter("other_airport", unloading_data["other_airport"]);
                pars[7].DbType = System.Data.DbType.String;
                pars[8] = new SqlParameter("other_seaport", unloading_data["other_seaport"]);
                pars[8].DbType = System.Data.DbType.String;

                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter p in pars)
                {
                    if (p != null)
                    {
                        if (p.DbType == System.Data.DbType.Int64)
                        {
                            if (p != null && p.Value != null)
                            {
                                if (p.Value.ToString() != "0" && p.Value.ToString() != "")
                                    cmd.Parameters.Add(p);
                            }
                        }
                        else
                        {
                            if (p != null && p.Value != null)
                            {
                                if (p.Value.ToString() != "0")
                                    cmd.Parameters.Add(p);
                            }
                        }
                    }
                }
                long.TryParse(cmd.ExecuteScalar().ToString(), out unloading_id);
            }
        }
    }

    public void InsertStep2CargoTransport(object step2, long app_id, long loading_id, long unloading_id, ref long step2_id)
    {
        Dictionary<string, object> step2_data = step2 as Dictionary<string, object>;
        SqlParameter[] pars = new SqlParameter[5];
        pars[0] = new SqlParameter("application_id", app_id);
        pars[0].DbType = System.Data.DbType.Int64;
        pars[1] = new SqlParameter("loading_id", loading_id);
        pars[1].DbType = System.Data.DbType.Int64;
        pars[2] = new SqlParameter("unloading_id", unloading_id);
        pars[2].DbType = System.Data.DbType.Int64;        
        pars[3] = new SqlParameter("loading_type_id", step2_data["loading_type_id"]);
        pars[3].DbType = System.Data.DbType.Int32;
        pars[4] = new SqlParameter("step2_id", step2_data["step2_id"]);
        pars[4].DbType = System.Data.DbType.Int32;

        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_InsStep2CargoTransport", conn))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter p in pars)
                {
                    if (p != null && p.Value != null)
                    {
                        if (p.Value.ToString() != "0")
                            cmd.Parameters.Add(p);
                    }
                }

                long.TryParse(cmd.ExecuteScalar().ToString(), out step2_id);
            }
        }
    }

    public void InsertStep3Air(object step3, long app_id)
    {
        Dictionary<string, object> step3_data = step3 as Dictionary<string, object>;
        SqlParameter[] pars = new SqlParameter[16];
        pars[0] = new SqlParameter("app_id", app_id);
        pars[0].DbType = System.Data.DbType.Int64;
        pars[1] = new SqlParameter("airport_loading_id", 0);
        pars[1].DbType = System.Data.DbType.Int64;
        pars[2] = new SqlParameter("airport_unloading_id", 0);
        pars[2].DbType = System.Data.DbType.Int64;
        pars[3] = new SqlParameter("level_importance_id", step3_data["level_importance_id"]);
        pars[3].DbType = System.Data.DbType.Int64;
        pars[4] = new SqlParameter("universal_type_id", step3_data["universal_type_id"]);
        pars[4].DbType = System.Data.DbType.Int64;
        pars[5] = new SqlParameter("placement_type_id", step3_data["placement_type_id"]);
        pars[5].DbType = System.Data.DbType.Int64;
        pars[6] = new SqlParameter("packing_type_id", step3_data["packing_type_id"]);
        pars[6].DbType = System.Data.DbType.Int64;
        pars[7] = new SqlParameter("length", step3_data["length"]);
        pars[7].DbType = System.Data.DbType.String;
        pars[8] = new SqlParameter("width", step3_data["width"]);
        pars[8].DbType = System.Data.DbType.String;
        pars[9] = new SqlParameter("height", step3_data["height"]);
        pars[9].DbType = System.Data.DbType.String;
        pars[10] = new SqlParameter("weight", step3_data["weight"]);
        pars[10].DbType = System.Data.DbType.String;
        pars[11] = new SqlParameter("volume", step3_data["volume"]);
        pars[11].DbType = System.Data.DbType.String;
        pars[12] = new SqlParameter("price", step3_data["price"]);
        pars[12].DbType = System.Data.DbType.String;
        pars[13] = new SqlParameter("special_conditions_id", step3_data["special_conditions_id"]);
        pars[13].DbType = System.Data.DbType.Int64;
        pars[14] = new SqlParameter("other_information", step3_data["other_information"]);
        pars[14].DbType = System.Data.DbType.String;
        pars[15] = new SqlParameter("type", step3_data["type"]);
        pars[15].DbType = System.Data.DbType.Int64;

        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_InsStep3Air", conn))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter p in pars)
                {
                    if (p != null && p.Value != null)
                    {
                        if (p.Value.ToString() != "0")
                            cmd.Parameters.Add(p);
                    }
                }
                cmd.ExecuteScalar().ToString();
            }
        }
    }

    public void InsertStep3Transport(object step3, long app_id)
    {
        Dictionary<string, object> step3_data = step3 as Dictionary<string, object>;
        SqlParameter[] pars = new SqlParameter[13];
        pars[0] = new SqlParameter("app_id", app_id);
        pars[0].DbType = System.Data.DbType.Int64;
        pars[1] = new SqlParameter("level_importance_id", step3_data["level_importance_id"]);
        pars[1].DbType = System.Data.DbType.Int64;
        pars[2] = new SqlParameter("universal_type_id", step3_data["universal_type_id"]);
        pars[2].DbType = System.Data.DbType.Int64;
        pars[3] = new SqlParameter("placement_type_id", step3_data["placement_type_id"]);
        pars[3].DbType = System.Data.DbType.Int64;
        pars[4] = new SqlParameter("packing_type_id", step3_data["packing_type_id"]);
        pars[4].DbType = System.Data.DbType.Int64;
        pars[5] = new SqlParameter("length", step3_data["length"]);
        pars[5].DbType = System.Data.DbType.String;
        pars[6] = new SqlParameter("width", step3_data["width"]);
        pars[6].DbType = System.Data.DbType.String;
        pars[7] = new SqlParameter("height", step3_data["height"]);
        pars[7].DbType = System.Data.DbType.String;
        pars[8] = new SqlParameter("weight", step3_data["weight"]);
        pars[8].DbType = System.Data.DbType.String;
        pars[9] = new SqlParameter("volume", step3_data["volume"]);
        pars[9].DbType = System.Data.DbType.String;
        pars[10] = new SqlParameter("price", step3_data["price"]);
        pars[10].DbType = System.Data.DbType.String;
        pars[11] = new SqlParameter("special_conditions_id", step3_data["special_conditions_id"]);
        pars[11].DbType = System.Data.DbType.Int64;
        pars[12] = new SqlParameter("other_information", step3_data["other_information"]);
        pars[12].DbType = System.Data.DbType.String;

        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_InsStep3Transport", conn))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter p in pars)
                {
                    if (p != null && p.Value != null)
                    {
                        if (p.Value.ToString() != "0")
                            cmd.Parameters.Add(p);
                    }
                }
                cmd.ExecuteScalar().ToString();
            }
        }
    }

    public void InsStep3SeaCargo(object step3, long app_id)
    {
        Dictionary<string, object> step3_data = step3 as Dictionary<string, object>;
        SqlParameter[] pars = new SqlParameter[15];
        pars[0] = new SqlParameter("app_id", app_id);
        pars[0].DbType = System.Data.DbType.Int64;
        pars[1] = new SqlParameter("seaport_loading_id", 0);
        pars[1].DbType = System.Data.DbType.Int64;
        pars[2] = new SqlParameter("seaport_unloading_id", 0);
        pars[2].DbType = System.Data.DbType.Int64;
        pars[3] = new SqlParameter("level_importance_id", step3_data["level_importance_id"]);
        pars[3].DbType = System.Data.DbType.Int64;
        pars[4] = new SqlParameter("universal_type_id", step3_data["universal_type_id"]);
        pars[4].DbType = System.Data.DbType.Int64;
        pars[5] = new SqlParameter("placement_type_id", step3_data["placement_type_id"]);
        pars[5].DbType = System.Data.DbType.Int64;
        pars[6] = new SqlParameter("packing_type_id", step3_data["packing_type_id"]);
        pars[6].DbType = System.Data.DbType.Int64;
        pars[7] = new SqlParameter("length", step3_data["length"]);
        pars[7].DbType = System.Data.DbType.String;
        pars[8] = new SqlParameter("width", step3_data["width"]);
        pars[8].DbType = System.Data.DbType.String;
        pars[9] = new SqlParameter("height", step3_data["height"]);
        pars[9].DbType = System.Data.DbType.String;
        pars[10] = new SqlParameter("weight", step3_data["weight"]);
        pars[10].DbType = System.Data.DbType.String;
        pars[11] = new SqlParameter("volume", step3_data["volume"]);
        pars[11].DbType = System.Data.DbType.String;
        pars[12] = new SqlParameter("price", step3_data["price"]);
        pars[12].DbType = System.Data.DbType.String;
        pars[13] = new SqlParameter("special_conditions_id", step3_data["special_conditions_id"]);
        pars[13].DbType = System.Data.DbType.Int64;
        pars[14] = new SqlParameter("other_information", step3_data["other_information"]);
        pars[14].DbType = System.Data.DbType.String;
        
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_InsStep3SeaCargo", conn))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter p in pars)
                {
                    if (p != null && p.Value != null)
                    {
                        if (p.Value.ToString() != "0")
                            cmd.Parameters.Add(p);
                    }
                }
                cmd.ExecuteScalar().ToString();
            }
       }
    }

    public void InsStep3SeaTransport(object step3, long app_id)
    {
        Dictionary<string, object> step3_data = step3 as Dictionary<string, object>;
        SqlParameter[] pars = new SqlParameter[15];
        pars[0] = new SqlParameter("app_id", app_id);
        pars[0].DbType = System.Data.DbType.Int64;
        pars[1] = new SqlParameter("country_id", step3_data["country_id"]);
        pars[1].DbType = System.Data.DbType.Int64;
        pars[2] = new SqlParameter("seaport_id", step3_data["seaport_id"]);
        pars[2].DbType = System.Data.DbType.Int64;
        pars[3] = new SqlParameter("universal_type_id", step3_data["universal_type_id"]);
        pars[3].DbType = System.Data.DbType.Int64;
        pars[4] = new SqlParameter("vessel_build_date", DateTime.Now/*step3_data["vessel_build_date"]*/);
        pars[4].DbType = System.Data.DbType.DateTime;
        pars[5] = new SqlParameter("class", step3_data["clas"]);
        pars[5].DbType = System.Data.DbType.String;
        pars[6] = new SqlParameter("date_duration", DateTime.Now/*step3_data["date_duration"]*/);
        pars[6].DbType = System.Data.DbType.DateTime;
        pars[7] = new SqlParameter("time_duration", step3_data["time_duration"]);
        pars[7].DbType = System.Data.DbType.String;
        pars[8] = new SqlParameter("price", step3_data["price"]);
        pars[8].DbType = System.Data.DbType.String;
        pars[9] = new SqlParameter("special_conditions_id", step3_data["special_conditions_id"]);
        pars[9].DbType = System.Data.DbType.Int64;
        pars[10] = new SqlParameter("other_information", step3_data["other_information"]);
        pars[10].DbType = System.Data.DbType.String;
        pars[11] = new SqlParameter("flag_country_id", step3_data["flag_country_id"]);
        pars[11].DbType = System.Data.DbType.Int64;
        pars[12] = new SqlParameter("level_importance_id", step3_data["level_importance_id"]);
        pars[12].DbType = System.Data.DbType.Int64;
        pars[13] = new SqlParameter("placement_type_id", step3_data["placement_type_id"]);
        pars[13].DbType = System.Data.DbType.Int64;
       
        string routes = "";
        if (step3_data["route"] != null && step3_data["route"].ToString()!="")
        {
            IEnumerator len = ((object[])step3_data["route"]).GetEnumerator();
            while (len.MoveNext())
            {
                routes += len.Current + ",";
            }
        }
        pars[14] = new SqlParameter("route", routes);
        pars[14].DbType = System.Data.DbType.String;

        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_InsStep3SeaTransport", conn))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter p in pars)
                {
                    if (p != null && p.Value != null)
                    {
                        if (p.Value.ToString() != "0")
                            cmd.Parameters.Add(p);
                    }
                }
                cmd.ExecuteScalar().ToString();
            }
        }
    }

    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string UpdateStep3SeaTransport(object step3)
    {
        Dictionary<string, object> step3_data = step3 as Dictionary<string, object>;
        SqlParameter[] pars = new SqlParameter[16];
        pars[0] = new SqlParameter("app_id", step3_data["app_id"]);
        pars[0].DbType = System.Data.DbType.Int64;
        pars[1] = new SqlParameter("country_id", step3_data["country_id"]);
        pars[1].DbType = System.Data.DbType.Int64;
        pars[2] = new SqlParameter("seaport_id", step3_data["seaport_id"]);
        pars[2].DbType = System.Data.DbType.Int64;
        pars[3] = new SqlParameter("universal_type_id", step3_data["universal_type_id"]);
        pars[3].DbType = System.Data.DbType.Int64;
        pars[4] = new SqlParameter("vessel_build_date", DateTime.Now/*step3_data["vessel_build_date"]*/);
        pars[4].DbType = System.Data.DbType.DateTime;
        pars[5] = new SqlParameter("class", step3_data["clas"]);
        pars[5].DbType = System.Data.DbType.String;
        pars[6] = new SqlParameter("date_duration", DateTime.Now/*step3_data["date_duration"]*/);
        pars[6].DbType = System.Data.DbType.DateTime;
        pars[7] = new SqlParameter("time_duration", step3_data["time_duration"]);
        pars[7].DbType = System.Data.DbType.String;
        pars[8] = new SqlParameter("price", step3_data["price"]);
        pars[8].DbType = System.Data.DbType.String;
        pars[9] = new SqlParameter("special_conditions_id", step3_data["special_conditions_id"]);
        pars[9].DbType = System.Data.DbType.Int64;
        pars[10] = new SqlParameter("other_information", step3_data["other_information"]);
        pars[10].DbType = System.Data.DbType.String;
        pars[11] = new SqlParameter("flag_country_id", step3_data["flag_country_id"]);
        pars[11].DbType = System.Data.DbType.Int64;
        pars[12] = new SqlParameter("level_importance_id", step3_data["level_importance_id"]);
        pars[12].DbType = System.Data.DbType.Int64;
        pars[13] = new SqlParameter("placement_type_id", step3_data["placement_type_id"]);
        pars[13].DbType = System.Data.DbType.Int64;
        pars[14] = new SqlParameter("id", step3_data["id"]);
        pars[14].DbType = System.Data.DbType.Int64;

        string routes = "";
        if (step3_data["route"] != null && step3_data["route"].ToString() != "")
        {
            IEnumerator len = ((object[])step3_data["route"]).GetEnumerator();
            while (len.MoveNext())
            {
                routes += len.Current + ",";
            }
        }
        pars[15] = new SqlParameter("route", routes);
        pars[15].DbType = System.Data.DbType.String;

        string msg = "";
        long sea_port_id = 0;
        try
        {
            if (Int64.TryParse(step3_data["id"].ToString(), out sea_port_id))
            {
                using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand("SP_UpdateStep3SeaTransport", conn))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        foreach (SqlParameter p in pars)
                        {
                            if (p != null && p.Value != null)
                            {
                                if (p.Value.ToString() != "0")
                                    cmd.Parameters.Add(p);
                            }
                        }
                        cmd.ExecuteScalar();
                        msg = "Application successfully updated.";
                    }
                }
            }
            else{
                msg = "Invalid Parametar for id";
            }
            JavaScriptSerializer js = new JavaScriptSerializer();
            string strJSON = js.Serialize(msg);
            return strJSON;      
        }
        catch (Exception ex)
        {
            JavaScriptSerializer js = new JavaScriptSerializer();
            string strJSON = js.Serialize(ex.Message);
            return strJSON;
        }

    }

    public void InsertStep3Tender(object step3, long app_id)
    {
        Dictionary<string, object> step3_data = step3 as Dictionary<string, object>;
        SqlParameter[] pars = new SqlParameter[11];
        pars[0] = new SqlParameter("application_id", app_id);
        pars[0].DbType = System.Data.DbType.Int64;
        DateTime opening_date;
        if (DateTime.TryParse(step3_data["opening_date"].ToString(), out opening_date))
        {
            pars[1] = new SqlParameter("opening_date", opening_date);
            pars[1].DbType = System.Data.DbType.DateTime;
        }
        DateTime closing_date;
        if (DateTime.TryParse(step3_data["closing_date"].ToString(), out closing_date))
        {
            pars[2] = new SqlParameter("closing_date", closing_date);
            pars[2].DbType = System.Data.DbType.DateTime;
        }
        pars[3] = new SqlParameter("transport_type_id", step3_data["transport_type_id"]);
        pars[3].DbType = System.Data.DbType.Int64;
        pars[4] = new SqlParameter("packing_type_id", step3_data["packing_type_id"]);
        pars[4].DbType = System.Data.DbType.Int64;
        pars[5] = new SqlParameter("type_goods_id", step3_data["type_goods_id"]);
        pars[5].DbType = System.Data.DbType.Int64;
        pars[6] = new SqlParameter("weight_goods", step3_data["weight_goods"]);
        pars[6].DbType = System.Data.DbType.String;
        pars[7] = new SqlParameter("payment_type_id", step3_data["payment_type_id"]);
        pars[7].DbType = System.Data.DbType.String;
        pars[8] = new SqlParameter("special_condition_type", step3_data["special_condition_type"]);
        pars[8].DbType = System.Data.DbType.Int64;
        pars[9] = new SqlParameter("other_information", step3_data["other_information"]);
        pars[9].DbType = System.Data.DbType.String;
        pars[10] = new SqlParameter("placement_type_id", step3_data["placement_type_id"]);
        pars[10].DbType = System.Data.DbType.Int64;

        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_InsStep3Tender", conn))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter p in pars)
                {
                    if (p != null && p.Value != null)
                    {
                        if (p.Value.ToString() != "0")
                            cmd.Parameters.Add(p);
                    }
                }
                cmd.ExecuteScalar().ToString();
            }
        }
    }

    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string UpdateStep3Tender(object step3)
    {
        Dictionary<string, object> step3_data = step3 as Dictionary<string, object>;
        long tender_id = 0;
        SqlParameter[] pars = new SqlParameter[12];
        pars[0] = new SqlParameter("application_id", step3_data["app_id"]);
        pars[0].DbType = System.Data.DbType.Int64;
        DateTime opening_date;
        if (DateTime.TryParse(step3_data["opening_date"].ToString(), out opening_date))
        {
            pars[1] = new SqlParameter("opening_date", opening_date);
            pars[1].DbType = System.Data.DbType.DateTime;
        }
        DateTime closing_date;
        if (DateTime.TryParse(step3_data["closing_date"].ToString(), out closing_date))
        {
            pars[2] = new SqlParameter("closing_date", closing_date);
            pars[2].DbType = System.Data.DbType.DateTime;
        }
        pars[3] = new SqlParameter("transport_type_id", step3_data["transport_type_id"]);
        pars[3].DbType = System.Data.DbType.Int64;
        pars[4] = new SqlParameter("packing_type_id", step3_data["packing_type_id"]);
        pars[4].DbType = System.Data.DbType.Int64;
        pars[5] = new SqlParameter("type_goods_id", step3_data["type_goods_id"]);
        pars[5].DbType = System.Data.DbType.Int64;
        pars[6] = new SqlParameter("weight_goods", step3_data["weight_goods"]);
        pars[6].DbType = System.Data.DbType.String;
        pars[7] = new SqlParameter("payment_type_id", step3_data["payment_type_id"]);
        pars[7].DbType = System.Data.DbType.String;
        pars[8] = new SqlParameter("special_condition_type", step3_data["special_condition_type"]);
        pars[8].DbType = System.Data.DbType.Int64;
        pars[9] = new SqlParameter("other_information", step3_data["other_information"]);
        pars[9].DbType = System.Data.DbType.String;
        pars[10] = new SqlParameter("id", step3_data["id"]);
        pars[10].DbType = System.Data.DbType.Int64;
        pars[11] = new SqlParameter("placement_type_id", step3_data["placement_type_id"]);
        pars[11].DbType = System.Data.DbType.Int64;

        string msg = "";
        try
        {
            if (Int64.TryParse(step3_data["id"].ToString(), out tender_id))
            {
                using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand("SP_UpdateStep3Tender", conn))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        foreach (SqlParameter p in pars)
                        {
                            if (p != null && p.Value != null)
                            {
                                if (p.Value.ToString() != "0")
                                    cmd.Parameters.Add(p);
                            }
                        }
                        cmd.ExecuteScalar();
                        msg = "Application successfully updated.";
                    }
                }
            }
            else{
                msg = "Invalid Parametar for id";
            }
            JavaScriptSerializer js = new JavaScriptSerializer();
            string strJSON = js.Serialize(msg);
            return strJSON;
        }
        catch(Exception ex)
        {
            JavaScriptSerializer js = new JavaScriptSerializer();
            string strJSON = js.Serialize(ex.Message);
            return strJSON;
        }
    }

    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public void SendEmail(long app_id, long type_transport_id, long type_offer_id)
    {
        string loading_date = "";
        string loading_time = "";
        string loading_coutry = "";
        string loading_city = "";
        string loading_post_code = "";
        string loading_seaport_loading = "";
        string loading_aiport_loading = "";

        string unloading_date = "";
        string unloading_time = "";
        string unloading_coutry = "";
        string unloading_city = "";
        string unloading_post_code = "";
        string unloading_seaport_loading = "";
        string unloading_aiport_loading = "";

        string level_of_imp = "";
        string vehicle_type = "";
        string placement_type = "";
        string pack_type = "";
        string spec_cond = "";
        string lenght = "";
        string width = "";
        string height = "";
        string volume = "";
        string price = "";
        string other_info = "";

        string company_name = "";
        string company_email = "";
        string company_coutry = "";
        string company_city = "";
        string company_postcode = "";
        string company_address = "";
        string company_type = "";
        string company_opp_licence = "";
        string company_type_of_transport = "";
        string company_opp_activity = "";

        string contact_telefon = "";
        string contact_fax = "";
        string contact_mobile = "";
        string contact_website = "";
        string contact_contact_person = "";
        string contact_languages = "";
        string contact_jobtile = "";

        bool load_tech_info = false;
        if (type_offer_id != 71)//Tender
        {
            switch (type_transport_id)
            {
                case 164://Road
                case 166://Rail
                case 167://Air
                    load_tech_info = true;
                break;
                case 165://Sea
                    if (type_offer_id==69)//Cargo
                        load_tech_info = true;
                break;
            }
        }

        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Fn_ApplicationById(" + app_id + ")", conn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {

                    using (SqlCommand cmdLoading = new SqlCommand("SELECT * FROM Fn_getLoadingUnloadingByAppId(" + app_id + ")", conn))
                    {
                        SqlDataReader drLoadung = cmdLoading.ExecuteReader();
                        int count = 0;
                        while (drLoadung.Read())
                        {
                            count++;
                            if (count == 1)
                            {
                                //Loading
                                DateTime date_var;
                                loading_date = "N/A";
                                if (DateTime.TryParse(drLoadung["loading_date"].ToString(), out date_var))
                                {
                                    loading_date = date_var.ToShortDateString();
                                }
                                loading_time = drLoadung["loading_time"].ToString() != "" ? drLoadung["loading_time"].ToString() : "N/A";
                                Int64 _country_id = 0;
                                loading_coutry = "N/A";
                                if (Int64.TryParse(drLoadung["loading_country_id"].ToString(), out _country_id))
                                {
                                    string country_loading = LoadCountryById(_country_id, false);
                                    string country_short = country_loading;
                                    if (country_loading.Length > 14)
                                    {
                                        country_short = country_loading.Substring(0, 14) + "...";
                                    }
                                    loading_coutry = "<img width=\"20\" title='" + country_loading +
                                    "' height=\"12\" src=\"images/countries/" + drLoadung["loading_country_code"].ToString() + "-t.gif\" /> - " +
                                    drLoadung["loading_country_code"].ToString() + " " + country_short;

                                }
                                loading_city = drLoadung["loading_city"].ToString() != "" ? drLoadung["loading_city"].ToString() : "N/A";
                                loading_post_code = drLoadung["loading_postcode"].ToString() != "" ? drLoadung["loading_postcode"].ToString() : "N/A";
                                if (type_transport_id == 167)
                                {

                                    loading_aiport_loading = drLoadung["air_loading_country_name"].ToString() != ""
                                        ?  drLoadung["air_loading_country_name"].ToString() : "N/A";
                                }
                                if (type_transport_id == 165)
                                {
                                    loading_seaport_loading = drLoadung["sea_loading_country_name"].ToString() != ""
                                        ?  drLoadung["sea_loading_country_name"].ToString() : "N/A";
                                }

                                //UnLoading    
                                loading_date = "N/A";
                                if (DateTime.TryParse(drLoadung["unloading_date"].ToString(), out date_var))
                                {
                                    unloading_date = date_var.ToShortDateString();
                                }
                                unloading_time = drLoadung["unloading_time"].ToString() != "" ? drLoadung["unloading_time"].ToString() : "N/A";

                                loading_coutry = "N/A";
                                if (Int64.TryParse(drLoadung["unloading_country_id"].ToString(), out _country_id))
                                {
                                    string country_loading = LoadCountryById(_country_id, false);
                                    string country_short = country_loading;
                                    if (country_loading.Length > 14)
                                    {
                                        country_short = country_loading.Substring(0, 14) + "...";
                                    }
                                    unloading_coutry = "<img width=\"20\" title='" + country_loading +
                                    "' height=\"12\" src=\"images/countries/" + drLoadung["unloading_country_code"].ToString() + "-t.gif\" /> - " +
                                    drLoadung["unloading_country_code"].ToString() + " " + country_short;

                                }
                                unloading_city = drLoadung["unloading_city"].ToString() != "" ? drLoadung["unloading_city"].ToString() : "N/A";
                                unloading_post_code = drLoadung["unloading_postcode"].ToString() != "" ? drLoadung["unloading_postcode"].ToString() : "N/A";
                                if (type_transport_id == 167)
                                {

                                    unloading_aiport_loading = drLoadung["air_unloading_country_name"].ToString() != ""
                                        ?  drLoadung["air_unloading_country_name"].ToString() : "N/A";
                                }
                                if (type_transport_id == 165)
                                {
                                    unloading_seaport_loading = drLoadung["sea_unloading_country_name"].ToString() != ""
                                        ?  drLoadung["sea_unloading_country_name"].ToString() : "N/A";
                                }
                            }
                        }
                        drLoadung.Close();
                    }

                    if (load_tech_info)
                    {
                        //Load Techical Info
                        using (SqlCommand cmdTech = new SqlCommand("SP_GetTechnicalInfo", conn))
                        {
                            cmdTech.CommandType = System.Data.CommandType.StoredProcedure;
                            cmdTech.Parameters.Add("@app_id", DbType.Int64).Value = app_id;
                            cmdTech.Parameters.Add("@type_transport_id", DbType.Int64).Value = type_transport_id;
                            cmdTech.Parameters.Add("@type_offer_id", DbType.Int64).Value = type_offer_id;
                            SqlDataReader drTech = cmdTech.ExecuteReader();

                            while (drTech.Read())
                            {
                                long level_importance_id;
                                level_of_imp = "N/A";
                                if (drTech["level_importance_id"] != null)
                                {
                                    if (long.TryParse(drTech["level_importance_id"].ToString(), out level_importance_id))
                                    {
                                        level_of_imp = LoadUniversalType(level_importance_id);
                                    }
                                }
                                long universal_type_id;
                                vehicle_type = "N/A";
                                if (long.TryParse(drTech["universal_type_id"].ToString(), out universal_type_id))
                                {
                                    vehicle_type = LoadUniversalType(universal_type_id);
                                }
                                long placement_type_id;
                                placement_type = "N/A";
                                if (long.TryParse(drTech["placement_type_id"].ToString(), out placement_type_id))
                                {
                                    placement_type = LoadUniversalType(placement_type_id);
                                }
                                long packing_type_id;
                                pack_type = "N/A";
                                if (long.TryParse(drTech["packing_type_id"].ToString(), out packing_type_id))
                                {
                                    pack_type = LoadUniversalType(packing_type_id);
                                }
                                long special_conditions_id;
                                spec_cond = "N/A";
                                if (long.TryParse(drTech["special_conditions_id"].ToString(), out special_conditions_id))
                                {
                                    spec_cond = LoadUniversalType(special_conditions_id);
                                }

                                volume = drTech["volume"].ToString() != "" ? drTech["volume"].ToString() : "N/A";
                                price = drTech["price"].ToString() != "" ? drTech["price"].ToString() : "N/A";
                                width = drTech["width"].ToString() != "" ? drTech["width"].ToString() : "N/A";
                                lenght = drTech["length"].ToString() != "" ? drTech["length"].ToString() : "N/A";
                                height = drTech["height"].ToString() != "" ? drTech["height"].ToString() : "N/A";
                                other_info = drTech["other_information"].ToString() != "" ? drTech["other_information"].ToString() : "N/A";
                            }
                            drTech.Close();
                        }
                    }
                   
                    //Company Informations
                    company_name = dr["company_name"].ToString() != "" ? dr["company_name"].ToString() : "N/A";
                    company_email = dr["email"].ToString() != "" ? dr["email"].ToString() : ""; 
                    long country_id = 0;
                    company_coutry = "N/A";
                    if (Int64.TryParse(dr["country_id"].ToString(), out country_id))
                    {
                        string comp_country = LoadCountryById(country_id, false);
                        string short_code = LoadCountryById(country_id, true);
                        company_coutry = "<img width=\"20\" title='" + comp_country +
                        "' height=\"12\" src=\"images/countries/" + short_code + "-t.gif\" /> - " +
                         short_code + " " + comp_country;

                    }
                    company_city = dr["City"].ToString() != "" ? dr["City"].ToString() : "N/A";
                    company_postcode = dr["postcode"].ToString() != "" ? dr["postcode"].ToString() : "N/A";
                    company_address = dr["address"].ToString() != "" ? dr["address"].ToString() : "N/A";
                    company_type = dr["company_type"].ToString() != "" ? dr["company_type"].ToString() : "N/A";
                    company_opp_licence = dr["operating_licence"].ToString() != "" ? dr["operating_licence"].ToString() : "N/A";
                    company_opp_activity = dr["operating_activity"].ToString() != "" ? dr["operating_activity"].ToString() : "N/A";

                    using (SqlCommand cmdCompT = new SqlCommand("SP_TransportTypesByCompany", conn))
                        {
                            cmdCompT.CommandType = System.Data.CommandType.StoredProcedure;
                            cmdCompT.Parameters.Add("id", dr["company_id"].ToString());
                            cmdCompT.Parameters.Add("language_id", 1);
                            SqlDataReader drcompT = cmdCompT.ExecuteReader();
                            if (drcompT.HasRows)
                            {
                                while (drcompT.Read())
                                {
                                    company_type_of_transport += drcompT["name"] + "; ";
                                }
                            }
                            else
                            {
                                company_type_of_transport = "N/A";
                            }

                            drcompT.Close();
                        }
                        //Contact details
                        contact_telefon = dr["telephone"].ToString() != "" ? dr["telephone"].ToString() : "N/A";
                        contact_fax = dr["fax"].ToString() != "" ? dr["fax"].ToString() : "N/A";
                        contact_mobile = dr["mobile"].ToString() != "" ? dr["mobile"].ToString() : "N/A";
                        contact_website = dr["website"].ToString() != "" ? dr["website"].ToString() : "N/A";
                        contact_contact_person = dr["contact_person"].ToString() != "" ? dr["contact_person"].ToString() : "N/A";
                        contact_jobtile = dr["job_title"].ToString() != "" ? dr["job_title"].ToString() : "N/A";
                        if (dr["language"] != null)
                        {
                            string[] languages = dr["language"].ToString().Split(',');
                            string langs = "";
                            for (int i = 0; i < languages.Length; i++)
                            {
                                long lang_id = 0;
                                if (Int64.TryParse(languages[i], out lang_id))
                                {
                                    langs += LoadCountryById(lang_id, true) + " - " + LoadCountryById(lang_id, false)+"; ";
                                }
                            }
                            if (langs.Length>2)
                            contact_languages = langs.ToUpper().Substring(0, langs.Length - 2);
                        }
                    }
                    dr.Close();
                }
                
            }

        //get mail settings
        string senderMailAdress = ConfigurationManager.AppSettings["senderMailAdress"].ToString();
        string host = ConfigurationManager.AppSettings["host"].ToString();
        //string username = ConfigurationManager.AppSettings["username"].ToString();
        //string password = ConfigurationManager.AppSettings["password"].ToString();

        //create new mail message
        MailMessage mail = new MailMessage();

        //create mail message body
        mail.IsBodyHtml = true;
        mail.BodyEncoding = System.Text.Encoding.UTF8;

        StringBuilder str = new StringBuilder();
        str.Append("<html><head>");
        str.Append("<style> body{font-family: \"Calibri\",\"sans-serif\";font-size: 11pt;margin: 0 0 0.0001pt;}</style>");
        str.Append("</head><body>");
        str.Append("<h2 style='text-align:center'>www.TransportExchange.org</h2>");

        str.Append("Thank you for applying.<br />");
        str.Append("Your application has been successfully sent.<br />");
        str.Append("This is the information that you have entered in our system:<br />");

        str.Append("<h4>LOADING:</h4>");
        str.Append("<b>Date:</b> " + loading_date + "<br />");
        str.Append("<b>Time:</b> " + loading_time + "<br />");
        str.Append("<b>Country:</b> " + loading_coutry + "<br />");
        str.Append("<b>City:</b> " + loading_city + "<br />");
        str.Append("<b>Post code:</b> " + loading_post_code + "<br />");
        if (type_transport_id == 165)
        {
            str.Append("<b>Seaport Loading:</b> " + loading_seaport_loading + "<br />");
        }
        if (type_transport_id == 167)
        {
            str.Append("<b>Airport Loading:</b> " + loading_aiport_loading + "<br />");
        }

        str.Append("<h4>UNLOADING:</h4>");
        str.Append("<b>Date:</b> " + unloading_date + "<br />");
        str.Append("<b>Time:</b> " + unloading_time + "<br />");
        str.Append("<b>Country:</b> " + unloading_coutry + "<br />");
        str.Append("<b>City:</b> " + unloading_city + "<br />");
        str.Append("<b>Post code:</b> " + unloading_post_code + "<br />");
        if (type_transport_id == 165)
        {
            str.Append("<b>Seaport Unloading:</b> " + unloading_seaport_loading + "<br />");
        }
        if (type_transport_id == 167)
        {
            str.Append("<b>Airport Unloading:</b> " + unloading_aiport_loading + "<br />");
        }

        str.Append("<h4>TECHICAL INFORMATION</h4>");
        str.Append("<b>Level of importance:</b> "+level_of_imp+"<br />");
        str.Append("<b>Vehicle Type:</b>" + vehicle_type + "<br />");
        str.Append("<b>Placement Type:</b>" + placement_type + "<br />");
        str.Append("<b>Packing Type:</b>" + pack_type + "<br />");
        str.Append("<b>Special Conditions:</b>" + spec_cond + "<br />");
        str.Append("<b>Lenght:</b>" + lenght + "<br />");
        str.Append("<b>Width:</b>" + width + "<br />");
        str.Append("<b>Height:</b>" + height + "<br />");
        str.Append("<b>Volume:</b>" + volume+ "<br />");
        str.Append("<b>Price:</b>" + price + "<br />");
        str.Append("<b>Other Informattions:</b>" + other_info + "<br />");

        str.Append("<h4>COMPANY INFORMATION</h4>");
        str.Append("<b>Company name:</b>" + company_name + "<br />");
        str.Append("<b>Country:</b>" + company_coutry + "<br />");
        str.Append("<b>City:</b>" + company_city + "<br />");
        str.Append("<b>Post code:</b>" + company_postcode + "<br />");
        str.Append("<b>Address:</b>" + company_address + "<br />");
        str.Append("<b>Type of Company:</b>" + company_type + "<br />");
        str.Append("<b>Operating Licence:</b>" + company_opp_licence + "<br />");
        str.Append("<b>Type of transport:</b>" + company_type_of_transport + "<br />");
        str.Append("<b>Operating Activity Information:</b>" + company_opp_activity + "<br />");

        str.Append("<h4>CONTACT DETAILS</h4>");
        str.Append("<b>Telephone:</b>" + contact_telefon + "<br />");
        str.Append("<b>Fax:</b>" + contact_fax + "<br />");
        str.Append("<b>Mobile:</b>" + contact_mobile + "<br />");
        str.Append("<b>Website:</b>" + contact_website + "<br />");
        str.Append("<b>Contact person:</b>" + contact_contact_person + "<br />");
        str.Append("<b>Language(s):</b>" + contact_languages + "<br />");
        str.Append("<b>Job Title:</b>" + contact_jobtile + "<br />");

        str.Append("<br />");
        str.Append("<h3>Transport Exchange</h3>");
        str.Append("Str. 710 No. 1 1000 Skopje - Macedonia<br />");
        str.Append("Tel +389 2 2551 057<br />");
        str.Append("Fax +389 2 2549 016<br />");
        str.Append("Cell. Tel. +389 78399019<br />");
        str.Append("e-mail: <a href='mailto:info@transportexchange.org'>info@transportexchange.org</a><br />");
        str.Append("skype: transportexchange<br />");
        str.Append("Website: <a href='www.transportexchange.org'>www.transportexchange.org</a><br />");
        str.Append("</body></html>");

        if (company_email != "")
        {
            mail.Body = str.ToString();

            //set mail message properties
            mail.To.Add(new MailAddress(company_email));
            //mail.Bcc.Add("info@transportexchange.org");
            mail.From = new MailAddress(senderMailAdress);
            mail.Subject = "TransportExchange.org Application";

            //create new SmtpClient
            SmtpClient smtpClient = new SmtpClient();

            //set SmtpClient properties
            smtpClient.Port = 25;
            smtpClient.Host = host;
            string username = ConfigurationManager.AppSettings["username"].ToString();
            string password = ConfigurationManager.AppSettings["password"].ToString();
            smtpClient.Credentials = new System.Net.NetworkCredential(username, password);

            smtpClient.Send(mail);
        }

        /*System.IO.StreamWriter file = new System.IO.StreamWriter(@"E:\Projects\newpr\transport-exchange\src\test.html");
        file.Write(str.ToString());
        file.Close();*/
       
    }

    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    private string LoadCountryById(long id, bool short_name)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT id, short, name FROM dbo.Fn_GetPlace(@id)", conn))
            {
                cmd.Parameters.Add("id", id);
                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    if (short_name)
                        return dr["short"].ToString();
                    else
                        return dr["name"].ToString();
                }
                dr.Close();
                return "";
            }
        }
    }

    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    private string LoadUniversalType(long id)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            string cmd1 = "";
            Int64 langId = DAO.Connection.DEFAULT_LANGUAGE;
            
            if (langId != DAO.Connection.DEFAULT_LANGUAGE)
                cmd1 = "SELECT id, name FROM dbo.Fn_GetUniversalTypeByLang(@id,@language_id)";
            else
                cmd1 = "SELECT id, name FROM dbo.Fn_GetUniversalTypeById(@id,@language_id)";
            using (SqlCommand cmd = new SqlCommand(cmd1, conn))
            {
                cmd.Parameters.Add("id", id);
                cmd.Parameters.Add("language_id", langId);

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    if (dr["name"]!=null)
                    {
                        return dr["name"].ToString();
                    }   
                }
                dr.Close();
                return "";
            }
        }
    }

    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string LoadVehicleTypes(string cat_id)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            string cmd1 = "";
            Dictionary<string, string> res = new Dictionary<string, string>();
            Int64 langId = DAO.Connection.DEFAULT_LANGUAGE;

            cmd1 = "SELECT TOP 1000 [id],[name],[isActive],[language_id],[parent_id],[type],[type_id] FROM [UniversalType] where type_id=14 and language_id=1 and isActive=1 and type like '%"+cat_id+"%'";
            using (SqlCommand cmd = new SqlCommand(cmd1, conn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    res.Add(dr["id"].ToString(), dr["name"].ToString());
                    //if (dr["name"] != null)
                    //{
                    //    return dr["name"].ToString();
                    //}
                }
                dr.Close();
            }
            JavaScriptSerializer js = new JavaScriptSerializer();
            string strJSON = js.Serialize(res);
            return strJSON;
        }
    }
}

